package service;

import model.Grade;
import utils.DatabaseUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

public class GradeService {

    /**
     * 获取所有成绩
     * @return List<Grade> 包含所有成绩的列表
     */
    public List<Grade> getAllGrades() {
        List<Grade> grades = new ArrayList<>();
        // SQL 查询语句，连接多个表以获取成绩详情
        String sql = "SELECT g.*, s.student_no, s.name as student_name, " +
                "sub.subject_name FROM grades g " +
                "JOIN students s ON g.student_id = s.id " +
                "JOIN subjects sub ON g.subject_id = sub.id " +
                "ORDER BY g.exam_date DESC";

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             Statement stmt = conn.createStatement(); // 创建 SQL 语句对象
             ResultSet rs = stmt.executeQuery(sql)) { // 执行查询并获取结果集

            while (rs.next()) {
                Grade grade = new Grade(); // 创建 Grade 对象
                // 设置 Grade 对象的各个属性
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setStudentNo(rs.getString("student_no"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setSubjectId(rs.getInt("subject_id"));
                grade.setSubjectName(rs.getString("subject_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setExamType(rs.getString("exam_type"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setCreatedAt(rs.getTimestamp("created_at"));
                grades.add(grade); // 将 Grade 对象添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return grades; // 返回成绩列表
    }

    /**
     * 根据班级ID获取成绩
     * @param classId 班级ID
     * @return List<Grade> 包含指定班级成绩的列表
     */
    public List<Grade> getGradesByClass(int classId) {
        List<Grade> grades = new ArrayList<>();
        // SQL 查询语句，通过班级ID连接多个表以获取成绩详情
        String sql = "SELECT g.*, s.student_number, s.name as student_name, " +
                "c.class_name, sub.subject_name " +
                "FROM grades g " +
                "JOIN students s ON g.student_id = s.id " +
                "JOIN classes c ON s.class_id = c.id " +
                "JOIN subjects sub ON g.subject_id = sub.id " +
                "WHERE c.id = ? " +
                "ORDER BY g.exam_date DESC, s.student_number";

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 创建预编译的 SQL 语句对象

            pstmt.setInt(1, classId); // 设置参数
            ResultSet rs = pstmt.executeQuery(); // 执行查询并获取结果集

            while (rs.next()) {
                // 通过结果集创建 Grade 对象，并设置其属性
                Grade grade = new Grade(
                        rs.getInt("id"),
                        rs.getInt("student_id"),
                        rs.getString("student_number"),
                        rs.getString("student_name"),
                        rs.getString("class_name"),
                        rs.getInt("subject_id"),
                        rs.getString("subject_name"),
                        rs.getDouble("score"),
                        rs.getString("exam_type"),
                        rs.getDate("exam_date"),
                        rs.getTimestamp("created_at")
                );
                grades.add(grade); // 将 Grade 对象添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return grades; // 返回成绩列表
    }

    /**
     * 获取成绩统计数据
     * @param classId 班级ID，可选
     * @param subjectId 科目ID，可选
     * @param examType 考试类型，可选
     * @return Map<String, Object> 包含成绩统计数据的键值对
     */
    public Map<String, Object> getGradeStatistics(Integer classId, Integer subjectId, String examType) {
        Map<String, Object> stats = new HashMap<>();
        // SQL 查询语句，用于获取成绩统计信息
        String sql = "SELECT COUNT(*) as total, " +
                "AVG(score) as average, " +
                "MAX(score) as highest, " +
                "MIN(score) as lowest, " +
                "SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) as passed " +
                "FROM grades WHERE 1=1";

        List<Object> params = new ArrayList<>();
        // 根据传入的参数动态添加 WHERE 子句条件
        if (classId != null) {
            sql += " AND class_id = ?";
            params.add(classId);
        }
        if (subjectId != null) {
            sql += " AND subject_id = ?";
            params.add(subjectId);
        }
        if (examType != null && !"全部".equals(examType)) {
            sql += " AND exam_type = ?";
            params.add(examType);
        }

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql)) { // 创建预编译的 SQL 语句对象

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i)); // 设置参数
            }

            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            if (rs.next()) {
                // 从结果集中读取统计数据并存入 Map
                stats.put("total", rs.getInt("total"));
                stats.put("average", rs.getDouble("average"));
                stats.put("highest", rs.getDouble("highest"));
                stats.put("lowest", rs.getDouble("lowest"));
                stats.put("passed", rs.getInt("passed"));
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return stats; // 返回统计数据 Map
    }

    /**
     * 获取班级排名
     * @param gradeId 成绩ID
     * @return int 指定成绩在班级中的排名
     */
    public int getClassRank(int gradeId) {
        // SQL 查询语句，使用窗口函数计算成绩在班级中的排名
        String sql = "SELECT rank FROM (" +
                "  SELECT id, RANK() OVER (PARTITION BY subject_id, class_id " +
                "    ORDER BY score DESC) as rank " +
                "  FROM grades g " +
                "  JOIN students s ON g.student_id = s.id" +
                ") ranked WHERE id = ?";

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 创建预编译的 SQL 语句对象

            pstmt.setInt(1, gradeId); // 设置参数
            ResultSet rs = pstmt.executeQuery(); // 执行查询并获取结果集

            if (rs.next()) {
                return rs.getInt("rank"); // 返回排名
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }

        return 0; // 默认返回排名为0
    }

    /**
     * 获取年级排名
     * @param gradeId 成绩ID
     * @return int 指定成绩在年级中的排名
     */
    public int getGradeRank(int gradeId) {
        // SQL 查询语句，使用窗口函数计算成绩在年级中的排名
        String sql = "SELECT rank FROM (" +
                "  SELECT id, RANK() OVER (PARTITION BY subject_id " +
                "    ORDER BY score DESC) as rank " +
                "  FROM grades" +
                ") ranked WHERE id = ?";

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 创建预编译的 SQL 语句对象

            pstmt.setInt(1, gradeId); // 设置参数
            ResultSet rs = pstmt.executeQuery(); // 执行查询并获取结果集

            if (rs.next()) {
                return rs.getInt("rank"); // 返回排名
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }

        return 0; // 默认返回排名为0
    }

    /**
     * 根据筛选条件查询成绩
     * @param classId 班级ID，可选
     * @param subjectId 科目ID，可选
     * @param examType 考试类型，可选
     * @return List<Grade> 符合筛选条件的成绩列表
     */
    public List<Grade> queryGrades(Integer classId, Integer subjectId, String examType) {
        List<Grade> grades = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT g.*, s.student_no, s.name as student_name, " +
                        "c.class_name, sub.subject_name " +
                        "FROM grades g " +
                        "JOIN students s ON g.student_id = s.id " +
                        "JOIN classes c ON s.class_id = c.id " +
                        "JOIN subjects sub ON g.subject_id = sub.id " +
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();
        // 动态添加 WHERE 子句条件
        if (classId != null) {
            sql.append(" AND s.class_id = ?");
            params.add(classId);
        }

        if (subjectId != null) {
            sql.append(" AND g.subject_id = ?");
            params.add(subjectId);
        }

        if (examType != null && !examType.equals("全部")) {
            sql.append(" AND g.exam_type = ?");
            params.add(examType);
        }

        sql.append(" ORDER BY g.exam_date DESC"); // 添加排序条件

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) { // 创建预编译的 SQL 语句对象

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i)); // 设置参数
            }

            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            while (rs.next()) {
                Grade grade = new Grade(); // 创建 Grade 对象
                // 设置 Grade 对象的各个属性
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setStudentNo(rs.getString("student_no"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setClassName(rs.getString("class_name"));
                grade.setMajorName(rs.getString("major_name")); // 注意：这里假设结果集中有 major_name 字段
                grade.setSubjectName(rs.getString("subject_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setExamType(rs.getString("exam_type"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setCreatedAt(rs.getDate("created_at")); // 注意：这里假设 created_at 字段为 Date 类型
                grades.add(grade); // 将 Grade 对象添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return grades; // 返回成绩列表
    }

    /**
     * 获取学生个人成绩
     * @param userId 学生用户ID
     * @return List<Map<String, Object>> 包含学生个人成绩的键值对列表
     */
    public List<Map<String, Object>> getPersonalGrades(int userId) {
        List<Map<String, Object>> grades = new ArrayList<>();
        // SQL 查询语句，通过用户ID获取学生个人成绩详情
        String sql = "SELECT s.subject_name, g.score, g.exam_type, g.exam_date " +
                "FROM students st " +
                "JOIN grades g ON st.id = g.student_id " +
                "JOIN subjects s ON g.subject_id = s.id " +
                "WHERE st.user_id = ? " +
                "ORDER BY g.exam_date DESC, s.subject_name";

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql)) { // 创建预编译的 SQL 语句对象

            stmt.setInt(1, userId); // 设置参数
            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集

            while (rs.next()) {
                Map<String, Object> grade = new HashMap<>(); // 创建 Map 对象
                // 从结果集中读取成绩详情并存入 Map
                grade.put("subjectName", rs.getString("subject_name"));
                grade.put("score", rs.getDouble("score"));
                grade.put("examType", rs.getString("exam_type"));
                grade.put("examDate", rs.getDate("exam_date"));
                grades.add(grade); // 将 Map 对象添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return grades; // 返回成绩详情列表
    }

    /**
     * 获取成绩数据（支持筛选和排序）
     * @param filters 筛选条件 Map
     * @param orderBy 排序条件字符串
     * @return List<Grade> 符合筛选条件并按照排序条件排序的成绩列表
     */
    public List<Grade> getGrades(Map<String, Object> filters, String orderBy) {
        List<Grade> grades = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT g.*, s.student_no, s.name as student_name, " +
                        "c.class_name, m.major_name, sub.subject_name, " +
                        "(SELECT COUNT(*) + 1 FROM grades g2 " +
                        "WHERE g2.score > g.score AND g2.subject_id = g.subject_id " +
                        "AND g2.exam_type = g.exam_type) as student_rank " +
                        "FROM grades g " +
                        "JOIN students s ON g.student_id = s.id " +
                        "JOIN classes c ON s.class_id = c.id " +
                        "JOIN majors m ON c.major_code = m.major_code " +
                        "JOIN subjects sub ON g.subject_id = sub.id " +
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();
        // 使用 StringBuilder 构建 WHERE 子句
        if (filters != null) {
            for (Map.Entry<String, Object> entry : filters.entrySet()) {
                if (entry.getValue() != null && !entry.getValue().toString().isEmpty()) {
                    // 根据筛选条件动态添加 WHERE 子句条件
                    sql.append(" AND ").append(getFilterCondition(entry.getKey()));
                    params.add(entry.getValue());
                }
            }
        }

        // 添加排序
        if (orderBy != null && !orderBy.isEmpty()) {
            sql.append(" ORDER BY ").append(orderBy);
        }

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) { // 创建预编译的 SQL 语句对象

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            // 设置较大的 fetch size 以减少数据库往返次数
            stmt.setFetchSize(1000);

            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            while (rs.next()) {
                // 通过结果集创建 Grade 对象，并设置其属性
                grades.add(mapResultSetToGrade(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }
        return grades; // 返回成绩列表
    }

    /**
     * 获取成绩统计信息
     * @param filters 筛选条件 Map
     * @return Map<String, Object> 包含成绩统计数据的键值对
     */
    public Map<String, Object> getGradeStats(Map<String, Object> filters) {
        Map<String, Object> stats = new HashMap<>();

        StringBuilder sql = new StringBuilder(
                "SELECT " +
                        "COUNT(*) as total_count, " +
                        "COALESCE(AVG(score), 0) as avg_score, " +  // 使用 COALESCE 处理 NULL
                        "COALESCE(MAX(score), 0) as max_score, " +
                        "COALESCE(MIN(score), 0) as min_score, " +
                        "COALESCE(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END), 0) as excellent_count, " +
                        "COALESCE(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END), 0) as pass_count, " +
                        "COALESCE(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END), 0) as range_90_100, " +
                        "COALESCE(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END), 0) as range_80_89, " +
                        "COALESCE(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END), 0) as range_70_79, " +
                        "COALESCE(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END), 0) as range_60_69, " +
                        "COALESCE(SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END), 0) as range_0_59 " +
                        "FROM grades g " +
                        "JOIN students st ON g.student_id = st.id " +
                        "JOIN classes c ON st.class_id = c.id " +
                        "JOIN majors m ON c.major_code = m.major_code " +  // 添加专业关联
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();
        // 动态添加 WHERE 子句条件
        if (filters != null) {
            if (filters.get("department") != null) {
                sql.append(" AND m.dept_code = (SELECT dept_code FROM departments WHERE dept_name = ?)");
                params.add(filters.get("department"));
            }
            if (filters.get("major") != null) {
                sql.append(" AND m.major_name = ?");
                params.add(filters.get("major"));
            }
            if (filters.get("className") != null) {
                sql.append(" AND c.class_name = ?");
                params.add(filters.get("className"));
            }
            if (filters.get("examType") != null && !filters.get("examType").equals("全部")) {
                sql.append(" AND g.exam_type = ?");
                params.add(filters.get("examType"));
            }
            if (filters.get("examDate") != null) {
                sql.append(" AND DATE(g.exam_date) = ?");
                params.add(filters.get("examDate"));
            }
        }

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) { // 创建预编译的 SQL 语句对象

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i)); // 设置参数
            }

            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            if (rs.next()) {
                int totalCount = rs.getInt("total_count");
                // 从结果集中读取统计数据并存入 Map
                stats.put("totalCount", totalCount);
                stats.put("avgScore", rs.getDouble("avg_score"));
                stats.put("maxScore", rs.getDouble("max_score"));
                stats.put("minScore", rs.getDouble("min_score"));
                stats.put("excellentCount", rs.getInt("excellent_count"));
                stats.put("passCount", rs.getInt("pass_count"));

                // 计算比率
                stats.put("excellentRate", totalCount > 0 ?
                        (rs.getDouble("excellent_count") * 100 / totalCount) : 0.0);
                stats.put("passRate", totalCount > 0 ?
                        (rs.getDouble("pass_count") * 100 / totalCount) : 0.0);

                // 分数段统计
                stats.put("range_90_100", rs.getInt("range_90_100"));
                stats.put("range_80_89", rs.getInt("range_80_89"));
                stats.put("range_70_79", rs.getInt("range_70_79"));
                stats.put("range_60_69", rs.getInt("range_60_69"));
                stats.put("range_0_59", rs.getInt("range_0_59"));
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }

        return stats; // 返回统计数据 Map
    }

    /**
     * 根据班级ID和筛选条件获取班级所有成绩（仅班主任可查看）
     * @param teacherId 教师ID
     * @param filters 筛选条件 Map
     * @return List<Grade> 符合筛选条件的成绩列表
     */
    public List<Grade> getClassGrades(int teacherId, Map<String, Object> filters) {
        List<Grade> grades = new ArrayList<>();

        // 首先检查是否是班主任
        String checkSql = "SELECT c.id FROM classes c WHERE c.teacher_id = ?";
        int classId = -1;

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(checkSql)) { // 创建预编译的 SQL 语句对象
            stmt.setInt(1, teacherId); // 设置参数
            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            if (rs.next()) {
                classId = rs.getInt("id"); // 获取班级ID
            } else {
                // 不是班主任，返回空列表
                return grades;
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
            return grades;
        }

        // 是班主任，查询本班所有成绩
        StringBuilder sql = new StringBuilder(
                "SELECT g.*, u.username as student_no, u.name as student_name, " +
                        "c.class_name, s.subject_name, s.credits, " +
                        "(SELECT COUNT(*) + 1 FROM grades g2 " +
                        "WHERE g2.subject_id = g.subject_id AND g2.exam_type = g.exam_type " +
                        "AND g2.score > g.score AND g2.student_id IN " +
                        "(SELECT st2.id FROM students st2 WHERE st2.class_id = c.id)) as class_rank, " +
                        "(SELECT AVG(g3.score) FROM grades g3 " +
                        "JOIN students st3 ON g3.student_id = st3.id " +
                        "WHERE g3.subject_id = g.subject_id AND g3.exam_type = g.exam_type " +
                        "AND st3.class_id = c.id) as class_avg_score " +
                        "FROM grades g " +
                        "JOIN students st ON g.student_id = st.id " +
                        "JOIN users u ON st.user_id = u.id " +
                        "JOIN classes c ON st.class_id = c.id " +
                        "JOIN subjects s ON g.subject_id = s.id " +
                        "WHERE c.id = ?"
        );

        List<Object> params = new ArrayList<>();
        params.add(classId);

        // 动态添加 WHERE 子句条件
        if (filters != null) {
            if (filters.get("examType") != null && !filters.get("examType").equals("全部")) {
                sql.append(" AND g.exam_type = ?");
                params.add(filters.get("examType"));
            }
            if (filters.get("examDate") != null) {
                sql.append(" AND DATE(g.exam_date) = ?");
                params.add(filters.get("examDate"));
            }
            if (filters.get("subjectId") != null) {
                sql.append(" AND g.subject_id = ?");
                params.add(filters.get("subjectId"));
            }
            if (filters.get("search") != null) {
                // 添加模糊搜索条件
                sql.append(" AND (u.username LIKE ? OR u.name LIKE ?)");
                String search = "%" + filters.get("search") + "%";
                params.add(search);
                params.add(search);
            }
        }

        // 添加排序
        sql.append(" ORDER BY g.exam_date DESC, s.subject_name, g.score DESC");

        try (Connection conn = DatabaseUtil.getConnection(); // 获取数据库连接
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) { // 创建预编译的 SQL 语句对象

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i)); // 设置参数
            }

            ResultSet rs = stmt.executeQuery(); // 执行查询并获取结果集
            while (rs.next()) {
                Grade grade = new Grade(); // 创建 Grade 对象
                // 设置 Grade 对象的各个属性
                grade.setId(rs.getInt("id"));
                grade.setStudentId(rs.getInt("student_id"));
                grade.setStudentNo(rs.getString("student_no"));
                grade.setStudentName(rs.getString("student_name"));
                grade.setClassName(rs.getString("class_name"));
                grade.setSubjectId(rs.getInt("subject_id"));
                grade.setSubjectName(rs.getString("subject_name"));
                grade.setScore(rs.getDouble("score"));
                grade.setExamType(rs.getString("exam_type"));
                grade.setExamDate(rs.getDate("exam_date"));
                grade.setRank(rs.getInt("class_rank")); // 班级排名
                grade.setAverageScore(rs.getDouble("class_avg_score")); // 班级平均分
                grade.setCredits(rs.getDouble("credits")); // 学分
                grades.add(grade); // 将 Grade 对象添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 打印 SQL 异常信息
        }

        return grades; // 返回成绩列表
    }

    /**
     * 获取 SQL 查询条件字符串
     * @param key 筛选条件键
     * @return String SQL 查询条件字符串
     */
    private String getFilterCondition(String key) {
        switch (key) {
            case "department":
                return "m.dept_code = (SELECT dept_code FROM departments WHERE dept_name = ?)";
            case "major":
                return "m.major_name = ?";
            case "className":
                return "c.class_name = ?";
            case "examType":
                return "g.exam_type = ?";
            case "examDate":
                return "DATE(g.exam_date) = ?";
            case "subjectId":
                return "g.subject_id = ?";
            case "studentNo":
                // 添加模糊搜索条件
                return "s.student_no LIKE ?";
            case "studentName":
                // 添加模糊搜索条件
                return "s.name LIKE ?";
            default:
                return "1=1";
        }
    }

    /**
     * 将 ResultSet 转换为 Grade 对象
     * @param rs 结果集
     * @return Grade 成绩对象
     * @throws SQLException 如果 SQL 操作失败
     */
    private Grade mapResultSetToGrade(ResultSet rs) throws SQLException {
        Grade grade = new Grade(); // 创建 Grade 对象
        // 设置 Grade 对象的各个属性
        grade.setId(rs.getInt("id"));
        grade.setStudentId(rs.getInt("student_id"));
        grade.setStudentNo(rs.getString("student_no"));
        grade.setStudentName(rs.getString("student_name"));
        grade.setClassName(rs.getString("class_name"));
        grade.setMajorName(rs.getString("major_name"));
        grade.setSubjectName(rs.getString("subject_name"));
        grade.setScore(rs.getDouble("score"));
        grade.setExamType(rs.getString("exam_type"));
        grade.setExamDate(rs.getDate("exam_date"));
        grade.setRank(rs.getInt("student_rank")); // 学生成绩排名
        return grade; // 返回 Grade 对象
    }
}
